完璧だと思うBigQueryをさらに性能よく使用するコツ【セッションレポート】#GoogleCloudNext東京

概要

Google Cloud Next 東京'23に参加した際のセッションレポートになります。
ただセッション内容を書くというよりかは、なるべく短くまとめてわかりやすいように工夫したいと思っています。

本ブログはデータ分析を加速するBigQuery性能加速化のポイントというセッションに参加した時の情報です。
前半の内容がBigQueryアーキテクチャの紹介で、後半にBigQueryをさらに性能よく使用するコツの話が出てきました。

今回は後半のBigQueryをさらに性能よく使用するコツに絞り、まとめていきます。

性能最適化のポイントは少ないリソースで結果を受け取ること

前提

BigQueryで異なるSQLでも、同様な結果を表示させることが可能です。
ただ、性能面を考慮するとなるべく少ないリソースを使用し、結果を受け取ることが鍵になると言います。

下記にまとめます。

• IO削減
• 通信量の削減
• コンピュートリソースの削減
• シリアル処理の削減
• 効率的なSQLを記述
• 効率的なテーブル設計
• 処理内容の見直し

これらを考慮して、さらにBigQueryを性能よく使用するコツについて言及していきます。

必要な列のみクエリ

基本的にはBigQuery、スキャンした分の課金が発生します。
よって、データの全ての列を取得するのではなく、必要な列のみを指定して取得する方法を採用すると、データ量を減らし、読み込み時間を短縮させるため、クエリの性能向上に寄与します。

例えば、SELECT * FROMの代わりに、SELECT column1, column2 FROMのようにクエリを書くことで実現します。

WHERE句で使用される列を考慮

BigQueryでクエリを実行する際に、WHEREを用いて特定の列に対するフィルタリングを行なう方法を指します。

結果のサイズを削減し、不必要なデータのスキャンを避けるため、処理速度に大きな影響を与えます。（クエリでフィルタリングされるデータが少ないと処理速度が上がる可能性あり）

さらに、BigQueryでは文字列（STRING）よりもブール（BOOL）、整数（INT）、浮動小数点数（FLOAT）、または日付（DATE）の方が扱いやすく、効率的になります。

ORDER BYの見直し

ORDER BY句は結果セットを、特定の列の値に基づいてソートするのに使用しますが、この操作は比較的高コストであり、クエリのパフォーマンスに大きな影響を与えます。

サブクエリでORDER BYを使用すると、結果セットがその後の処理で再ソートされる可能性があり、性能を低下させる可能性があるので、ORDER BYを使用する際には、最も外側のクエリ（最終的な結果セットのソートが必要な場所）で使用することで性能低下を回避します。

よって、上記画像を考慮することで、パフォーマンスの向上を見込めることができます。

大規模な結果セットの見直し

クエリにより生成される結果セットの大きさを理解し、それを可能な限り最小限に抑えることでコストやパフォーマンスを最適化します。
クエリが生み出す結果の量が多いほど、必要なリソース（時間とコンピュートパワー）が増え、コストも高くなりパフォーマンスにも影響を与えます。

結合順序の指定

テーブルの大きさに基づいて結合順序を決めるメリットとしては、最初に大きなテーブルを結合し、その後に小さなテーブルを結合することで、必要なスキャン量を減らしてくれます。

データ量が減少する順序で結合するメリットとしては、結合後のデータ量が小さくなるような順序で結合を行うことで、処理効率を改善することができます。

自己結合の回避

同じテーブルの異なる行を比較したり、結合したりするための自己結合クエリを避け、必要であれば書き直すこと提示しています。

大量のデータを扱う場合、自己結合を使用すると複雑さが増し、性能を大幅に低下させる可能性があります。

クロスジョインの回避

クロスジョインは大量のデータを生成する可能性があり、その結果パフォーマンスが大きく低下する可能性があります。
クロスジョインは計算量が大きくなりがちなので、可能な限り使用を避けるべきという考え方です。

クロスジョインの回避策の例

他にも、INNER JOINやLEFT JOINなどの結合型を適切に使用し、結果セットのサイズを制御したり、必要に応じてWHERE句やON句を使用して結合条件を指定し、必要な組み合わせのみを生成することなども可能なようです。

COUNT（DISTINCT）の見直し

COUNT(DISTINCT)は一般的に高負荷の操作で、性能に影響を与えます。よって、必要な場合にのみ使用し、可能な場合はその他の方法を探すことが推奨されます。

ちなみに、近似集計関数とは、実際の正確な結果を近似的に得るための関数のことで、APPROX_COUNT_DISTINCT()などがあります。

ウィンドウ関数の見直し

ウィンドウ関数は、結果セットのサブセット（ウィンドウ）上で集計を行う関数で、計算負荷が高い場合があります。

この関数は全体のデータセットに対する計算が必要なので、大きなデータ量の場合にはコストが高くなりますが、画像にあるようにLimitで並べ替えて、トップNに絞り込むという戦略を採用することで、ウィンドウ関数が計算するべきデータの量を減らすことができます。

最新レコードを取得する集計関数の見直し

BigQueryの集計関数の選択に関するパフォーマンス最適化の戦略です。
RANKやROW_NUMBERはウィンドウ関数であり、全体のデータセットに対する操作が必要なため、大規模なデータセットに対してはパフォーマンスが低下する可能性があります。

一方、min_byやmax_byは集計関数で、特定の列の最小値または最大値とそれに対応した他の列の値を取得することができます。

これらを使用すれば、特定の条件下での最新レコードを取得する際に、行全体のスキャンを行うRANKやROW_NUMBERよりも効率的に結果を得ることが可能です。

中間テーブルの利用

中間テーブルとは、一時的に生成されるテーブルで、一部の結果を保存したり、複数のクエリステップ間でデータを共有したりするために使用されます。

複雑なクエリをより単純な部分に分割し、それぞれの部分の結果を中間テーブルに保存します。これにより、各クエリが取り扱うデータ量を減らし、クエリの対象となるデータのスキャン範囲を小さくすることができます。

シリアル処理の削減

必要な処理を並列に行うことで、トータルの処理時間を短縮し、BigQueryのパフォーマンスを向上させるという戦略です。

具体的な効果については画像の通りですが、シリアル処理は並列処理に比べて時間がかかるため、可能な限りシリアル処理の削減を目指し、並列に実行できる操作や設計パターンを優先することが重要となります。

パーティショニング

パーティショニングは、大規模なデータセットを管理可能な小さな部分に分けることを指しこのプロセスを通じて、BigQueryは必要なデータセクションだけにクエリを実行することができ、結果的にクエリの実行時間が短縮され、コストも減少します。

よって、パーティショニングを使用することは、BigQueryでデータ管理を行う際の重要な戦略であり、性能向上とコスト節約の両方を実現します。

クラスタリング

クラスタリングを使用することは、特定の列を基にデータを物理的に並べ替えることを指し、クエリのパフォーマンスを向上させ、ストレージコストを削減します。

よって、クラスタリングを使用することは、BigQueryの性能を向上させるための重要な手法となり、データ管理の効率性とパフォーマンスに大きく寄与します。

マリアライズドビュー

マテリアライズドビューを使用することは、データベースのビュー（テーブルの仮想的なスナップショット）の結果セットを永続的に保存することを指します。

従来のビューとは異なり、マテリアライズドビューは基本テーブルのクエリ結果を物理的に保存し、結果セットの再計算を必要とせず、クエリのパフォーマンスを大幅に向上させることができます。

SEARCH INDEX

SEARCH INDEXとは、テーブルの列に対して、文字列の検索を高速化するためのインデックスです。

作成方法は、テーブルを選択し、検索したい列を選択して、SEARCH INDEXの作成を実行します。

メリットは、文字列の検索を高速化でき、クエリのパフォーマンスを向上できること、コストを削減できることです。

デメリットも調べてみると、作成に時間とコストがかかる、テーブルのサイズが大きくなることのようでした。

テーブルを非正規化する

テーブルを非正規化すると、関連するデータを同じテーブルに統合することができます。

データの重複が発生する可能性があるものの、JOIN操作の必要性が減少し、クエリのパフォーマンスが向上します。

主キーと外部キーで結合を最適化

主キーと外部キーを定義することで、関連するテーブル間の結合を高速化することを意味します。

関連するテーブルの両方に、主キーと外部キーを定義し、主キーは、テーブルのすべての行を一意に識別する列です。外部キーは、別のテーブルの主キーを参照する列です。

MetaData Caching

よく使われるデータやクエリの結果を一時的にキャッシュすることで、再利用時のパフォーマンスを向上させることができます。

次、同じクエリが実行されるとき、結果がキャッシュから直接取得できるため、データを再度計算する時間を節約することができます。

まとめ

かなり量が多かったのでキャッチアップが大変でした。
大雑把な解説、かつわかりにくい箇所もあるかと思いますが、少しでも参考になれば嬉しいです。

今回紹介した内容を、それぞれを深掘りすることで、さらに効率の良いBigQueryの運用を目指していけるかもしれません。